declare
mem_per int:=83; 
v_mem_mb int;
v_cpus int;
tname varchar(100);
MEMORY_POOL int;
MEMORY_N_POOLS int;
MEMORY_TARGET int;
BUFFER INT;
MAX_BUFFER INT;
RECYCLE int;
CACHE_POOL_SIZE int;
BUFFER_POOLS int;
RECYCLE_POOLS int;
SORT_BUF_SIZE int;
SORT_BUF_GLOBAL_SIZE INT;
DICT_BUF_SIZE  INT;
SESS_POOL_SIZE INT;
HJ_BUF_SIZE INT;
HAGR_BUF_SIZE INT;
HJ_BUF_GLOBAL_SIZE INT;
HAGR_BUF_GLOBAL_SIZE INT;
SORT_FLAG INT;
SORT_BLK_SIZE INT;
RLOG_POOL_SIZE INT;
FAST_POOL_PAGES INT default 3000;
FAST_ROLL_PAGES INT default 1000;


TASK_THREADS INT;
IO_THR_GROUPS INT;
CNT INT;

begin
    CNT :=0;
	SELECT TOP 1 N_CPU,TOTAL_PHY_SIZE/1024/1024 INTO v_cpus,v_mem_mb FROM V$SYSTEMINFO;
	
	print v_cpus;
	print v_mem_mb;
	
	v_mem_mb :=  v_mem_mb * (mem_per/100.0); -- 15000 ; --
	
	v_mem_mb=round(v_mem_mb,-3);
	print v_mem_mb;
	
	IF v_mem_mb <= 2000  THEN
	  return;
	END IF;
	
	IF v_mem_mb > 512000 THEN  
	   v_mem_mb :=v_mem_mb*0.8;
	END IF;
	
	MEMORY_TARGET=round(cast(v_mem_mb * 0.12 as int),-3);
	
	TASK_THREADS :=4;
	IO_THR_GROUPS :=4;
	IF v_cpus < 8  THEN   
	  TASK_THREADS :=4;
	  IO_THR_GROUPS :=2;
	END IF;
	
	IF v_cpus >= 64 THEN 
	   v_cpus := 64; 
	   TASK_THREADS :=16;
	   IO_THR_GROUPS :=8;
	END IF;
	
	
	
	
	BUFFER := round(cast(v_mem_mb * 0.4 as int),-3);
	MAX_BUFFER := BUFFER;
	
	RECYCLE :=cast(v_mem_mb * 0.04 as int);
	
	
  IF v_mem_mb < 70000 THEN
	
       with t as
        (
                select rownum rn from dual connect by level <= 100
        ) ,
        t1 as
        (
                select * from t where rn > 1 minus
                select
                        ta.rn * tb.rn
                from
                        t ta,
                        t tb
                where
                        ta.rn <= tb.rn
                    and ta.rn  > 1
                    and tb.rn  > 1
        )
      select top 1 rn into BUFFER_POOLS from t1 where rn > v_mem_mb/800 order by 1;
      
      PRINT 'BUFFER_POOLS ' || BUFFER_POOLS;
	   

	  --设置根据内存情况RECYCLE_POOLS参数
		with t as
        (
                select rownum rn from dual connect by level <= 100
        ) ,
        t1 as
        (
                select * from t where rn > 1 minus
                select
                        ta.rn * tb.rn
                from
                        t ta,
                        t tb
                where
                        ta.rn <= tb.rn
                    and ta.rn  > 1
                    and tb.rn  > 1
        )
      select top 1 rn into RECYCLE_POOLS from t1 where rn > v_mem_mb/800/3 order by 1;
      
      PRINT 'RECYCLE_POOLS ' || RECYCLE_POOLS;
      
        
    ELSE
       BUFFER_POOLS := 101;
       RECYCLE_POOLS := 41;
    END IF;
    
   
	
	--修改内存池
	IF v_mem_mb >= 16000  THEN 
          FAST_ROLL_PAGES:=32000/4;
          FAST_POOL_PAGES:=64000/4;
	   IF v_mem_mb= 16000 THEN
	      MEMORY_POOL := 1500;
	      SORT_BUF_GLOBAL_SIZE := 1000;
	   ELSE
	      MEMORY_POOL := 2000;
	      SORT_BUF_GLOBAL_SIZE := 2000;
	   END IF;
	   
	   CACHE_POOL_SIZE := 1024;
	   SORT_FLAG = 0;
	   SORT_BLK_SIZE=1;
	   SORT_BUF_SIZE := 10;
	   SORT_BUF_GLOBAL_SIZE := 2000;
	   SESS_POOL_SIZE := 16;
	   RLOG_POOL_SIZE := 1024;
	   
	   HJ_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),10000);
	   HAGR_BUF_GLOBAL_SIZE := LEAST(cast(v_mem_mb * 0.0625 as int),10000);
	   HJ_BUF_SIZE  :=250;
       HAGR_BUF_SIZE :=250;
       RECYCLE :=round(RECYCLE,-3);
	   
	   IF v_mem_mb > 64000 THEN
          FAST_ROLL_PAGES:=32000/2;
          FAST_POOL_PAGES:=64000/2;
	      CACHE_POOL_SIZE := 2048;
		  RLOG_POOL_SIZE := 2048;
	      SORT_FLAG = 1;
	      SORT_BLK_SIZE=1;
	      SORT_BUF_SIZE=50; 
	      SORT_BUF_GLOBAL_SIZE= cast(v_mem_mb * 0.02 as int);
	      SESS_POOL_SIZE := 32;  
	      
	      HJ_BUF_GLOBAL_SIZE := cast(v_mem_mb * 0.15625 as int);
	      HAGR_BUF_GLOBAL_SIZE := cast(v_mem_mb * 0.04 as int);
	      HJ_BUF_SIZE  :=512;
          HAGR_BUF_SIZE :=512;
	   END IF;

	   IF v_mem_mb > 128000 THEN
          FAST_ROLL_PAGES:=32000;
          FAST_POOL_PAGES:=64000;
       END IF;
	   
	   DICT_BUF_SIZE := 50;
       HJ_BUF_GLOBAL_SIZE :=round(HJ_BUF_GLOBAL_SIZE,-3);
       HAGR_BUF_GLOBAL_SIZE :=round(HAGR_BUF_GLOBAL_SIZE,-3);
       SORT_BUF_GLOBAL_SIZE :=round(SORT_BUF_GLOBAL_SIZE,-3);
       RECYCLE :=round(RECYCLE,-3);
	ELSE
	   MEMORY_POOL :=GREAT(cast(v_mem_mb * 0.0625 as int),100);
	   CACHE_POOL_SIZE := 200;
	   RLOG_POOL_SIZE  := 256;
	   SORT_BUF_SIZE := 10;
	   SORT_BUF_GLOBAL_SIZE := 500;
	   DICT_BUF_SIZE := 50;
	   SESS_POOL_SIZE =16;
	   SORT_FLAG = 0;
	   SORT_BLK_SIZE=1;
	   
	   HJ_BUF_GLOBAL_SIZE := GREAT(cast(v_mem_mb * 0.0625 as int),500);
	   HAGR_BUF_GLOBAL_SIZE := GREAT(cast(v_mem_mb * 0.0625 as int),500);
       HJ_BUF_SIZE := GREAT(cast(v_mem_mb * 0.00625 as int),50);
       HAGR_BUF_SIZE :=GREAT(cast(v_mem_mb * 0.00625 as int),50);
	END IF;	
	
	
	
	tname :='BAK_DMINI_' || to_char(sysdate,'yymmdd');
	
	select COUNT(*) INTO CNT from v$instance where mode$='STANDBY';
	IF CNT=0 THEN
		execute IMMEDIATE 'select count(*) from USER_ALL_TABLES where table_name= ?' into CNT using tname;
		IF CNT=0 THEN 
		execute IMMEDIATE 'CREATE TABLE BAK_DMINI_' || to_char(sysdate,'yymmdd') || ' as select *,sysdate uptime from v$dm_ini';
		ELSE 
		execute IMMEDIATE  'INSERT INTO  BAK_DMINI_' || to_char(sysdate,'yymmdd') || ' select *,sysdate uptime from v$dm_ini';
		END IF;
	END IF;
	
	MEMORY_N_POOLS :=MEMORY_POOL/200;
	print 'MEMORY_N_POOLS ' || MEMORY_N_POOLS;
	

	if exists(select 1 from v$dm_ini where para_name='MEMORY_N_POOLS') then
	  SP_SET_PARA_VALUE(2,'MEMORY_N_POOLS',      MEMORY_N_POOLS);	
	end if;

-- 2021年6月12日 自适应核查
with a as(
select 2 f1,'WORKER_THREADS' n,v_cpus v ,'' f union all
select 2 f1,'TASK_THREADS' n,TASK_THREADS v ,'' f union all
select 2 f1,'IO_THR_GROUPS' n,IO_THR_GROUPS v ,'' f union all
-- select 2 f1,'MAX_OS_MEMORY' n,       mem_per v ,'' f union all
select 2 f1,'MEMORY_POOL' n,         MEMORY_POOL v ,'' f union all
select 2 f1,'MEMORY_TARGET' n,       MEMORY_TARGET v ,'' f union all
select 2 f1,'BUFFER' n,              BUFFER v ,'' f union all
select 2 f1,'MAX_BUFFER' n,          MAX_BUFFER v ,'' f union all
select 2 f1,'BUFFER_POOLS' n,        BUFFER_POOLS v ,'' f union all
select 2 f1,'RECYCLE' n,        	   RECYCLE v ,'' f union all	
select 2 f1,'RECYCLE_POOLS' n,       RECYCLE_POOLS v ,'' f union all
select 2 f1,'HJ_BUF_GLOBAL_SIZE' n,  HJ_BUF_GLOBAL_SIZE v ,'' f union all
select 2 f1,'HJ_BUF_SIZE' n,        HJ_BUF_SIZE  v ,'' f union all
select 2 f1,'HAGR_BUF_GLOBAL_SIZE' n,HAGR_BUF_GLOBAL_SIZE v ,'' f union all
select 2 f1,'HAGR_BUF_SIZE' n,     HAGR_BUF_SIZE   v ,'' f union all
select 2 f1,'SORT_FLAG' n,SORT_FLAG v ,'' f union all
select 2 f1,'SORT_BLK_SIZE' n,SORT_BLK_SIZE v ,'' f union all
select 2 f1,'SORT_BUF_SIZE' n,       SORT_BUF_SIZE v ,'' f union all
select 2 f1,'SORT_BUF_GLOBAL_SIZE' n,       SORT_BUF_GLOBAL_SIZE v ,'' f union all
select 2 f1,'RLOG_POOL_SIZE' n,      RLOG_POOL_SIZE v ,'' f union all
select 2 f1,'SESS_POOL_SIZE' n,      SESS_POOL_SIZE v ,'' f union all
select 2 f1,'CACHE_POOL_SIZE' n,     CACHE_POOL_SIZE v ,'' f union all	
select 2 f1,'DICT_BUF_SIZE' n,       DICT_BUF_SIZE v ,'' f union all 
select 2 f1,'VM_POOL_TARGET' n,       16384 v ,'' f union all 
select 2 f1,'SESS_POOL_TARGET' n,       16384 v ,'' f union all 
select 2 f1,'USE_PLN_POOL' n,        1 v ,'' f union all 
select 2 f1,'ENABLE_MONITOR' n,      1 v ,'' f union all 
select 2 f1,'SVR_LOG' n,             0 v ,'' f union all 
select 2 f1,'TEMP_SIZE' n,           1024 v ,'' f union all
select 2 f1,'TEMP_SPACE_LIMIT' n,    102400 v ,'' f union all 
select 2 f1,'MAX_SESSIONS' n,        1500 v ,'' f union all 
select 2 f1,'MAX_SESSION_STATEMENT' n, 20000 v ,'' f union all 
select 2 f1,'PK_WITH_CLUSTER' n,		0 v ,'' f union all 
select 2 f1,'ENABLE_ENCRYPT' n,0 v ,'' f union all 
select 2 f1,'OLAP_FLAG' n,2 v ,'' f union all 
select 2 f1,'VIEW_PULLUP_FLAG' n,3 v ,'' f union all  
select 2 f1,'OPTIMIZER_MODE' n,1 v ,'' f union all 
select 2 f1,'ADAPTIVE_NPLN_FLAG' n,0 v ,'' f union all 
select 2 f1,'MAX_OS_MEMORY' n,100 v ,'' f union all
-- 2021年6月16日
select 2 f1,'FILE_SCAN_PERCENT' n,1.0 v ,'#percent of data scanned when calculating file used space' f union all
select 2,'ENABLE_FREQROOTS',1,'' union all 
select 2,'ENABLE_INJECT_HINT',1,'' union all  -- INJECT功能要打开
select 1 f1,'ALTER_TABLE_OPT' n ,2  v,'' union all
-- select 2,'REDOS_PRE_LOAD', 1,'' union all
-- select 2,'REDOS_PARALLEL_NUM', 11,'' union all
select 2,'RLOG_BUF_SIZE', 512,'' union all -- default 64
select 2,'IO_THR_GROUPS',32,'' union all -- 之前是16 来源于闪存阵列的测试数据 wizNote：20210502085304
select 2,'HIO_THR_GROUPS',32,'' union all -- 之前是16
select 2,'PARALLEL_POLICY',2,'' union all -- 默认是0，为了可以手动并行，这里设置为2
select 2,'FAST_POOL_PAGES',FAST_POOL_PAGES,'' union all 
select 2,'PARALLEL_PURGE_FLAG',1,'' union all  -- 默认是0  1）部分表count*比较慢，回滚段比较大？和undo_retention 以及v$purge的东西太多有关系？ 2）这个设置为0的时候，存在刚大量增删改的表，扫描很慢，因为待purge的东西比较多。
select 2,'FAST_ROLL_PAGES',FAST_ROLL_PAGES,''  
),end_ as(
select a.*,b.PARA_VALUE,b.FILE_VALUE,case when (v!=file_value or file_value is null) then case when b.para_name in ('FILE_SCAN_PERCENT') then 'sp_set_para_double_value(2,'''  else 'sp_set_para_value(2,'''  end ||n||''','''||v||''');' else null end  ff
from a,v$dm_ini b where a.n=b.para_name(+)
  )select ff||'--'||para_value||'--'||file_value from end_ where ff is not null
;
end;